const db = require("../config/db");
class Movie {
  static async findAll() {
    const [rows] = await db.query("SELECT * FROM movies");
    return rows;
  }

  static async findById(id) {
    const result = await db.query("SELECT * FROM movies WHERE id = ?", [id]);
    // 返回第一个匹配的电影，如果不存在则返回 null
    return result.length > 0 ? result[0] : null;
  }

  static async search(query, offset = 0, limit = 10) {
    if (query.startYear && isNaN(query.startYear)) {
      throw new Error('Invalid start year');
    }
    if (query.endYear && isNaN(query.endYear)) {
      throw new Error('Invalid end year');
    }
    // 计算总记录数
    let countSql = "SELECT COUNT(*) AS total FROM movies WHERE 1=1";
    const countParams = [];
    if (query.title) {
      countSql += " AND title LIKE ?";
      countParams.push(`%${query.title}%`);
    }
    if (query.tag) {
      countSql += " AND JSON_CONTAINS(`tags`, CAST(? AS JSON))";
      countParams.push(query.tag);
    }
    if (query.startYear || query.endYear) {
      if (query.startYear && query.endYear) {
        countSql += " AND release_year BETWEEN ? AND ?";
        countParams.push(query.startYear, query.endYear);
      } else if (query.startYear) {
        countSql += " AND release_year >= ?";
        countParams.push(query.startYear);
      } else {
        countSql += " AND release_year <= ?";
        countParams.push(query.endYear);
      }
    }
    const [countResult] = await db.query(countSql, countParams);
    const total = countResult[0].total;
    // 构建查询 SQL 和参数
    let sql = "SELECT * FROM movies WHERE 1=1";
    const params = [];
    if (query.title) {
      sql += " AND title LIKE ?";
      params.push(`%${query.title}%`);
    }
    if (query.tag) {
      sql += " AND JSON_CONTAINS(`tags`, CAST(? AS JSON))";
      params.push(query.tag);
    }
    if (query.startYear || query.endYear) {
      if (query.startYear && query.endYear) {
        sql += " AND release_year BETWEEN ? AND ?";
        params.push(query.startYear, query.endYear);
      } else if (query.startYear) {
        sql += " AND release_year >= ?";
        params.push(query.startYear);
      } else {
        sql += " AND release_year <= ?";
        params.push(query.endYear);
      }
    }
    sql += " LIMIT ? OFFSET ?";
    params.push(limit, offset);
    const [rows] = await db.query(sql, params);

    return { data: rows, total };
  }

  static async searchByRating(offset = 0, limit = 10) {
    const sql = "SELECT * FROM movies ORDER BY rating DESC LIMIT ? OFFSET ?";
    const [rows] = await db.query(sql, [limit, offset]);
    return rows;
  }

  static async searchByReviewsCount(offset = 0, limit = 10) {
    const sql = "SELECT * FROM movies ORDER BY count_reviews DESC LIMIT ? OFFSET ?";
    const [rows] = await db.query(sql, [limit, offset]);
    return rows;
  }

  static async create(movie) {
    // 插入电影基本信息
    const [result] = await db.query(
      "INSERT INTO movies (title, description, rating, poster_url, release_year, tags) VALUES (?, ?, ?, ?, ?, ?)",
      [
        movie.title,
        movie.description,
        movie.rating,
        movie.poster_url,
        movie.release_year,
        JSON.stringify(movie.tags), // 存储标签 ID 数组
      ]
    );
    const movieId = result.insertId;
    return movieId;
  }

  static async update(id, movie) {
    // 更新电影基本信息
    await db.query(
      "UPDATE movies SET title = ?, description = ?, rating = ?, poster_url = ?, release_year = ?, tags = ? WHERE id = ?",
      [
        movie.title,
        movie.description,
        movie.rating,
        movie.poster_url,
        movie.release_year,
        JSON.stringify(movie.tags), // 存储标签 ID 数组
        id,
      ]
    );
    return true;
  }

  static async delete(id) {
    try {
      // 删除评论
      await db.query("DELETE FROM reviews WHERE movie_id = ?", [id]);
      // 删除电影
      await db.query("DELETE FROM movies WHERE id = ?", [id]);
      return true;
    } catch (error) {
      throw error;
    }
  }
}

module.exports = Movie;
